
import java.sql.*;
import java.util.logging.Logger;

public class SimpleDB {
	private Connection con = null;;
	private Statement stmt = null;
	private PreparedStatement searchPnr = null;
	private PreparedStatement remove = null;
	private PreparedStatement add = null;
	private PreparedStatement all = null;
	private ResultSet m = null;

	public SimpleDB() {
		try {

			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			con = DriverManager.getConnection("jdbc:derby:testdb;create=true");
			stmt = con.createStatement();
			ResultSet result = stmt.executeQuery("SELECT TABLENAME FROM SYS.SYSTABLES");
			result.next();
			if (!result.getString(1).equals("ANMAL")) {
				stmt.executeUpdate("CREATE TABLE ANMAL (PNR VARCHAR(11), NAME VARCHAR(30), CLUB VARCHAR(15))");
			}
			searchPnr = con.prepareStatement("SELECT * FROM ANMAL WHERE PNR=?");
			remove = con.prepareStatement("DELETE FROM ANMAL WHERE PNR=?");
			add = con.prepareStatement("INSERT INTO ANMAL VALUES (?, ?, ?)");
			all = con.prepareStatement("SELECT * FROM ANMAL",
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} /*
			 * catch (ClassNotFoundException e) { // TODO Auto-generated catch
			 * block System.out.println(e.getMessage()); e.printStackTrace(); }
			 */catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public boolean resetTable() {
		try {
			Statement s = con.createStatement();
			s.executeUpdate("DELETE FROM ANMAL");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;

		// TODO Auto-generated method stub

	}

	public boolean addRecord(Record r) {
		try {
			add.setString(1, r.getPnr());
			add.setString(2, r.getName());
			add.setString(3, r.getClub());
			add.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;

		// TODO Auto-generated method stub

	}

	public Record[] allRecords() {
		Record[] rs = null;
		try {
			m = all.executeQuery();
			m.last();
			int num = m.getRow();
			rs = new Record[num];
			m.beforeFirst();
			for (int i = 0; m.next(); i++) {
				rs[i] = new Record(m.getString(1), m.getString(2), m
						.getString(3));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// TODO Auto-generated method stub
		return rs;
	}

	public void close() {
		try {

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// TODO Auto-generated method stub

	}

	public Record firstRecord() {
		Record tmp = null;
		if (m == null) {
			allRecords();
			try {
				m.beforeFirst();
				m.next();
				tmp = new Record(m.getString(1), m.getString(2), m.getString(3));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		// TODO Auto-generated method stub
		return tmp;
	}

	public boolean removeRecord(Record tmp) {
		try {
			remove.setString(1, tmp.getPnr());
			remove.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		// TODO Auto-generated method stub
		return true;
	}

	public Record searchRecord(String s) {
		Record r = null;
		try {
			searchPnr.setString(1, s);
			ResultSet rs = searchPnr.executeQuery();
			while (rs.next()) {
				r = new Record();
				r.setPnr(rs.getString("PNR"));
				r.setName(rs.getString("NAME"));
				r.setClub(rs.getString("CLUB"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// TODO Auto-generated method stub
		return r;
	}
	public Connection getCon() {
		return con;
	}

	public ResultSet getM() {
		return m;
	}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		SimpleDB d = new SimpleDB();
		d.addRecord(new Record("121212-1212","Pelle", "FBK"));
		//d.resetTable();
		Record[] rr = d.allRecords();

		for (int i = 0; i < rr.length; i++) {
			System.out.println(rr[i].getPnr() + " " + rr[i].getName() + " "
					+ rr[i].getClub());
		}
	}

	

}
